/**
 * Description:
 * Class Name:CachedRowSetPage
 * Date:2022/4/10 14:55
 *
 * @author LTisme
 * @version JAVA17
 */
package 疯狂Java讲义.第13章_MySQL数据库和JDBC编程._13_6_使用RowSet1_1来包装结果集._13_6_3_离线RowSet的查询分页;

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetProvider;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class CachedRowSetPage {
    private String driver;
    private String url;
    private String user;
    private String pwd;

    public void initParam(String paramFilePath) throws Exception{
        Properties props = new Properties();
        props.load(new FileReader(paramFilePath));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pwd = props.getProperty("pwd");
    }

    public CachedRowSet doQuery(String sql, int pageSize, int page) throws Exception{
        try (
                Connection connection = DriverManager.getConnection(url, user, pwd);
                Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                ResultSet resultSet = statement.executeQuery(sql)
                ){
            CachedRowSet cachedRowSet = RowSetProvider.newFactory().createCachedRowSet();
            // 设置每页显示pageSize条记录
            cachedRowSet.setPageSize(pageSize);
            // 使用ResultSet来装填CachedRowSet，并设置从第几条记录开始装填
            cachedRowSet.populate(resultSet, (page - 2) * pageSize + 1);
            return cachedRowSet;
        }
    }

    public static void main(String[] args) throws Exception{
        var crsp = new CachedRowSetPage();
        crsp.initParam("Chapter_13_mysql.ini");
        Class.forName(crsp.driver);

        String sql = "select * from student_table";
        var rs = crsp.doQuery(sql, 3, 2);
        // 向后滚动RowSet结果集
        while (rs.next()){
            System.out.println("打印第" + rs.getRow() + "行记录：" + rs.getString(1) + "\t"
                    + rs.getString(2) + "\t"
                    + rs.getString(3));
        }
    }
}
